﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using IDAL;
using Models;

namespace DAL
{
    public class WorksInfoDAL:IWorksInfo
    {

        public const string SQL_ONE_WORKSINFO = @"select * from WorksInfo where WorksInfo.WorksID = ";

        public const string SQL_FIRST_WORKPIC = @"select top 1 PicURL from WorksPicInfo where WorksID =";
        public const string SQL_FIRST_ORDER = @" order by PicLevel desc";

        private const string SQL_DECLARE = @"declare @precount int ";
        private const string SQL_PRE_COUNT = @"select @precount = count(*) from WorksInfo where WorksID < ";
        private const string SQL_NEXT_COUNT = @"select @precount = count(*) from WorksInfo where WorksID > ";
        private const string SQL_PRE_WORKINFO = @"select top 1 WorksID, CategoryID,@precount as precount from WorksInfo where WorksID <";
        private const string SQL_NEXT_WORKINFO = @"select top 1 WorksID, CategoryID,@precount as precount from WorksInfo where WorksID > ";
        private const string SQL_ORDER_BY = @" order by WorksID desc";
        private const string SQL_CATEGORYID = @" and CategoryID=";
        private const string SQL_WorkInfo_CountAdd = @"UPDATE [WorksInfo] SET [ClickCount] = [ClickCount] + 1 WHERE WorksID = ";
        
        public DataBase DB = new DataBase();

        public ArrayList GetPreWorksInfo(int nowWorksID,int categoryID)
        {
            DB.DatabaseConnection();
            ArrayList list = new ArrayList();
            string preSQL = "";
            if (categoryID == 0)
            {
                preSQL = SQL_DECLARE + SQL_NEXT_COUNT + nowWorksID + SQL_NEXT_WORKINFO + nowWorksID ;
            }
            else
            {
                preSQL = SQL_DECLARE + SQL_NEXT_COUNT + nowWorksID + SQL_NEXT_WORKINFO + nowWorksID + SQL_CATEGORYID + categoryID;
            }
            SqlDataReader reader = DB.GetdataRecords(preSQL);
            try
            {
                if (reader.Read())
                {
                    list.Add(Int32.Parse(reader["WorksID"].ToString().Trim()));
                    list.Add(Int32.Parse(reader["CategoryID"].ToString().Trim()));
                    list.Add(Int32.Parse(reader["precount"].ToString().Trim()));
                }
                else
                {
                    list.Add(0);
                    list.Add(0);
                    list.Add(0);
                }
            }
            catch
            {
                list.Add(0);
                list.Add(0);
                list.Add(0);
            }
            DB.CloseDB();
            return list;
        }

        public ArrayList GetNextWorksInfo(int nowWorksID,int categoryID)
        {
            ArrayList list = new ArrayList();
            DB.DatabaseConnection();
            WorksInfo worksinfo = new WorksInfo();
            string nextSQL = "";
            if (categoryID == 0)
            {
                nextSQL = SQL_DECLARE + SQL_PRE_COUNT + nowWorksID + SQL_PRE_WORKINFO + nowWorksID + SQL_ORDER_BY;
            }
            else
            {
                nextSQL = SQL_DECLARE + SQL_PRE_COUNT + nowWorksID + SQL_PRE_WORKINFO + nowWorksID +SQL_CATEGORYID+categoryID+ SQL_ORDER_BY;
            }
            SqlDataReader reader = DB.GetdataRecords(nextSQL);
            try
            {
                if (reader.Read())
                {
                    list.Add(Int32.Parse(reader["WorksID"].ToString().Trim()));
                    list.Add(Int32.Parse(reader["CategoryID"].ToString().Trim()));
                    list.Add(Int32.Parse(reader["precount"].ToString().Trim()));
                }
                else
                {
                    list.Add(0);
                    list.Add(0);
                    list.Add(0);
                }
            }
            catch
            {
                list.Add(0);
                list.Add(0);
                list.Add(0);
            }
            DB.CloseDB();
            return list;
        }
        public int GetPageCount(int pagesize,int CategoryID)
        {
            DB.DatabaseConnection();
            DB.SQLCommand = DB.DatabaseCon.CreateCommand();
            DB.SQLCommand.CommandType = CommandType.StoredProcedure;
            if (CategoryID == 0)
            {
                DB.SQLCommand.CommandText = "UP_WorksInfo_PAGECOUNT";
                DB.SQLCommand.Parameters.AddWithValue("@pagesize", pagesize);
            }
            else
            {
                DB.SQLCommand.CommandText = "UP_CategoryWorks_PAGECOUNT";
                DB.SQLCommand.Parameters.AddWithValue("@pagesize", pagesize);
                DB.SQLCommand.Parameters.AddWithValue(@"categoryid", CategoryID);
            }      
            //DB.SQLCommand.Parameters.AddWithValue("@pagesize", pagesize);
            DB.SQLCommand.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            DB.SQLCommand.ExecuteNonQuery();
            int n = (int)DB.SQLCommand.Parameters["@RETURN_VALUE"].Value;
            DB.CloseDB();
            return n;
        }
 
        public List<WorksInfo> GetAllWorksInfo(int pagesize, int nowpage)
        {
            List<WorksInfo> list = new List<WorksInfo>();
            DB.DatabaseConnection();
            SqlCommand cmd = DB.DatabaseCon.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UP_WorksInfo_PAGESELECT";
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            cmd.Parameters.AddWithValue("@nowpage", nowpage);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                WorksInfo worksInfo = new WorksInfo();
                ReadtoWorksInfo(reader, worksInfo);
                list.Add(worksInfo);
            }
            DB.CloseDB();
            return list;
        }

        public List<WorksInfo> GetCategoryWorksInfo(int CategoryID, int pagesize, int nowpage) 
        {
            List<WorksInfo> list = new List<WorksInfo>();
            DB.DatabaseConnection();
            SqlCommand cmd = DB.DatabaseCon.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UP_CategoryWorksInfo_PAGESELECT";
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            cmd.Parameters.AddWithValue("@categoryid", CategoryID);
            cmd.Parameters.AddWithValue("@nowpage", nowpage);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                WorksInfo worksInfo = new WorksInfo();
                ReadtoWorksInfo(reader, worksInfo);
                list.Add(worksInfo);
            }
            DB.CloseDB();
            return list;
        }

        public List<WorksInfo> GetOneWorksInfo(int WorksID)
        {
            List<WorksInfo> list = new List<WorksInfo>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_ONE_WORKSINFO+WorksID);
            try
            {
                if (reader.Read())
                {
                    WorksInfo worksinfo = new WorksInfo();
                    ReadtoWorksInfo(reader, worksinfo);
                    list.Add(worksinfo);
                }
            }
            catch
            {
                list = null;
            }
            DB.CloseDB();
            return list;
        }

        public string GetWorksFirstPic(int WorksID)
        {
            string firstPicURL = "";
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_FIRST_WORKPIC + WorksID + SQL_FIRST_ORDER);
            try
            {
                if (reader.Read())
                {
                    firstPicURL = reader["PicURL"] is DBNull ? "" : reader["PicURL"].ToString();
                }
            }
            catch { }
            DB.CloseDB();
            return firstPicURL;
        }

        public WorksInfo GetWorksInfo(int WorksID)
        {
            WorksInfo work = new WorksInfo();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_ONE_WORKSINFO + WorksID);
            try
            {
                if (reader.Read())
                {
                    ReadtoWorksInfo(reader, work);
                }
            }
            catch
            {
            }
            DB.CloseDB();
            return work;
        }
        public void ReadtoWorksInfo(SqlDataReader reader, WorksInfo worksInfo)
        {
            try
            { worksInfo.WorksID = Int32.Parse(reader["WorksID"].ToString()); }
            catch
            { }
            try
            { worksInfo.CategoryID = Int32.Parse(reader["CategoryID"].ToString()); }
            catch
            { }
            worksInfo.Title = reader["Title"] is DBNull ? "" : reader["Title"].ToString();
            worksInfo.WorkContent = reader["WorkContent"] is DBNull ? "" : reader["WorkContent"].ToString();
            worksInfo.CustomerName = reader["CustomerName"] is DBNull ? "" : reader["CustomerName"].ToString();
            try
            {
                worksInfo.PublishTime = (DateTime)reader["PublishTime"];
            }
            catch
            { }
            worksInfo.TeamWorkTime = reader["TeamWorkTime"] is DBNull ? "" : reader["TeamWorkTime"].ToString();
            try
            {
                worksInfo.IsNWorks = Int32.Parse(reader["IsNWorks"].ToString());
            }
            catch
            {
                
            }
            try
            {
                worksInfo.TopFlag = Int32.Parse(reader["TopFlag"].ToString());
            }
            catch { }
            try
            {
                worksInfo.HotFlag = Int32.Parse(reader["HotFlag"].ToString());
            }
            catch { }
            try
            {
                worksInfo.ClickCount = Int32.Parse(reader["ClickCount"].ToString());
            }
            catch { }
            worksInfo.PicURL = reader["PicURL"] is DBNull ? "" : reader["PicURL"].ToString();
            worksInfo.WorksBrief = reader["WorksBrief"] is DBNull ? "" : reader["WorksBrief"].ToString();
        }

        public int CountAddWorkInfo(int workID)
        {
            int isSuccess = -1;
            string sql = SQL_WorkInfo_CountAdd + workID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }

        public int AddWorksInfo(WorksInfo work)
        {
            int isSuccess = -1;
            string sql = @"INSERT INTO WorksInfo([CategoryID],[Title],[WorkContent],[CustomerName],[PublishTime],[IsNWorks],[TopFlag],[HotFlag],[ClickCount],[TeamWorkTime],[PicURL],[WorksBrief])
                        VALUES(" + work.CategoryID + @",
                               '" + work.Title + @"',
                               '" + work.WorkContent + @"',
                               '" + work.CustomerName + @"',
                               '" + work.PublishTime + @"',
                               " + work.IsNWorks + @",
                               " + work.TopFlag + @",
                               " + work.HotFlag + @",
                               0,
                               '" + work.TeamWorkTime + @"',
                               '" + work.PicURL + @"',
                               '" + work.WorksBrief + "');";
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        public int GetMaxID()
        {
            int maxid = 0;
            string sql = "SELECT MAX(WorksID) AS MAXID FROM WorksInfo ";
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                try
                {
                    maxid = Int32.Parse(reader["MAXID"].ToString());
                }
                catch { }
            }
            DB.CloseDB();
            return maxid;
        }
        public int UpdateWorksInfo(WorksInfo work)
        {
            int isSuccess = -1;
            string sql = @" UPDATE WorksInfo SET 
                        [CategoryID] = " + work.CategoryID + @",
                        [Title] = '" + work.Title + @"',
                        [WorkContent] = '" + work.WorkContent + @"',
                        [CustomerName] = '" + work.CustomerName + @"',
                        [TeamWorkTime] = '" + work.TeamWorkTime + @"',
                        [PicURL] = '" + work.PicURL + @"',
                        [WorksBrief] = '" + work.WorksBrief + @"'
                        WHERE WorksID = " + work.WorksID;
            //更新的时候不更新时间
            //[PublishTime] = '" + work.PublishTime + @"',
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        public int DeleteWorksInfo(int WorksID)
        {
            int isSuccess = -1;
            string sql = "DELETE FROM WorksInfo WHERE WorksID = " + WorksID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
    }
}
